package ru.neoflex.chugunov.database;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ru.neoflex.chugunov.Word;

public class WordJDBCDAO {

	Connection connection = null;
	PreparedStatement ptmt = null;
	ResultSet resultSet = null;

	public WordJDBCDAO() {
	}

	private Connection getConnection() throws SQLException {
		Connection conn;
		conn = ConnectionFactory.getInstance().getConnection();
		return conn;
	}

	public void add(Word word) {
		try {
			String queryString = "INSERT INTO WORDS(word, translation, transcription, count) VALUES (?,?,?,?)";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			ptmt.setString(1, word.getWord());
			ptmt.setString(2, word.getTranslation());
			ptmt.setString(3, word.getTranscription());
			ptmt.setInt(4, word.getCount());
			ptmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeDB();
		}
	}
	
	public void updateLastTimeWord(Word word) {
		try {
			String queryString = "UPDATE WORDS SET LASTTIME=? WHERE WORD=?";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			ptmt.setDate(1, (Date) word.getLastTime());
			ptmt.setString(2, word.getWord());
			ptmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeDB();
		}
	}
	
	public void updateWord(Word word) {
		try {
			String queryString = "UPDATE WORDS SET `LASTTIME`=?, `INTERVAL`=?, `EFACTOR`=?, `COUNT`=? WHERE WORD=?";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			ptmt.setDate(1, new java.sql.Date(word.getLastTimeMillis()));
			ptmt.setInt(2, word.getInterval());
			ptmt.setFloat(3, word.geteFactor());
			ptmt.setInt(4, word.getCount());
			ptmt.setString(5, word.getWord());
			ptmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeDB();
		}
	}

	public void addValue(Word word) {
		try {
			String queryString = "INSERT INTO WORDS (`word`, `lasttime`, `count`, `efactor`, `interval`) VALUES (?,?,?,?,?)";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			ptmt.setString(1, word.getWord());
			ptmt.setDate(2, new java.sql.Date(word.getLastTimeMillis()));
			ptmt.setInt(3, word.getCount());
			ptmt.setFloat(4, word.geteFactor());
			ptmt.setInt(5, word.getInterval());
			ptmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeDB();
		}
	}

	public void updateTranslation(Word word) {
		try {
			String queryString = "UPDATE WORDS SET TRANSLATION=? WHERE WORD=?";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			ptmt.setString(1, word.getTranslation());
			ptmt.setString(2, word.getWord());
			ptmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeDB();
		}
	}

	public List<Word> printAll() {
		ArrayList<Word> list = new ArrayList<>();
		try {
			String queryString = "SELECT `word`, `lasttime`, `translation`, `count`, `interval`, `efactor` FROM WORDS";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			resultSet = ptmt.executeQuery();
			while (resultSet.next()) {
				list.add(new Word(resultSet.getString("word"), resultSet.getDate("lasttime"), resultSet.getString("translation"),
						resultSet.getInt("count"), resultSet.getFloat("efactor"), resultSet.getInt("interval")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeDB();
		}
		return list;
	}

	public boolean chkValue(String str) {
		boolean chk = false;
		try {
			String queryString = "SELECT word FROM WORDS";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			resultSet = ptmt.executeQuery();
			while (resultSet.next()) {
				if (str.equals(resultSet.getString("word")))
					chk = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeDB();
		}
		return chk;
	}
	
	private void closeDB() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
			if (ptmt != null) {
				ptmt.close();
			}
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
